package idv.arthur.work;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

/**
 * Servlet implementation class GetTRlevel_01_body
 */
//@WebServlet(description = "取得測試結果畫面的第一層", urlPatterns = { "/GetTRlevel_01_body" })
public class GetTRlevel_01_body extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public GetTRlevel_01_body() {
        super();
    }

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		Widgets wid = new Widgets();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		String strSQL = "SELECT tcuid FROM tms_mrtcmapping WHERE tpid = ? AND mrid = ? ";
		int tpid = Integer.parseInt(request.getParameter("tpid"));
		String returnResult = "";
		String mrid = request.getParameter("mrid");
		String submittedBy = request.getParameter("submittedBy");
		ArrayList<TRlevel_01_body> trlevel_01_bodys = new ArrayList<TRlevel_01_body>();
		String comment = "";
		
		
		try {
			conn = wid.getConn(true);
			ps = conn.prepareStatement(strSQL);
			ps.setInt(1, tpid);
			ps.setString(2, mrid);
			rs2 = ps.executeQuery();
			while (rs2.next()) {
				strSQL = "SELECT t1.ver,t1.tpid,t1.mrid,t1.tcuid,t1.ver,innerTable.edition,innerTable.trSN,innerTable.trValue,innerTable.trName,innerTable.retenum,innerTable.succnum,innerTable.failnum,innerTable.cantnum,innerTable.waitnum,t2.numKeyPoint,t2.tcName, t2.tcKeyPoint, t2.tcResExp FROM tms_mrtcmapping t1 LEFT JOIN (SELECT t3.sn uniq,t4.sn a,t4.value trValue,t4.name trName,t3.tpid t,t3.mrid m,t3.ver v,t3.edition edition, t3.trSN trSN,t3.retenum retenum,t3.succnum succnum,t3.failnum failnum,t3.cantnum cantnum,t3.waitnum waitnum,t3.tcuid tcuid FROM tms_mrtr t3,tms_trresulttype t4 WHERE t3.trSN=t4.sn) innerTable ON ( t1.tpid=innerTable.t AND t1.mrid=innerTable.m AND t1.ver=innerTable.v AND t1.tcuid = innerTable.tcuid ),tms_testcase t2 WHERE t1.tpid=? AND t1.mrid=? AND t1.tcuid=? AND t1.tcuid = t2.uID ORDER BY innerTable.uniq DESC";
				ps = conn.prepareStatement(strSQL);
				ps.setInt(1, tpid);
				ps.setString(2, mrid);
				ps.setInt(3, rs2.getInt("tcuid"));
				rs = ps.executeQuery();
				
				int ver = 0;
				
				/** 利用IF 與 SQL語句的降冪排序，確保必定取得最新的 **/
				if (rs.next()){
					ver=rs.getInt("ver");
					TRlevel_01_body trlevel_01_body = new TRlevel_01_body();
					trlevel_01_body.setEdition(rs.getString("edition"));
					trlevel_01_body.setTrSN(rs.getInt("trSN"));
					trlevel_01_body.setTrValue(rs.getString("trValue"));
					trlevel_01_body.setTrName(rs.getString("trName"));
					trlevel_01_body.setRetenum(rs.getInt("retenum"));
					trlevel_01_body.setSuccnum(rs.getInt("succnum"));
					trlevel_01_body.setFailnum(rs.getInt("failnum"));
					trlevel_01_body.setCantnum(rs.getInt("cantnum"));
					trlevel_01_body.setWaitnum(rs.getInt("waitnum"));
					trlevel_01_body.setNumKeyPoint(rs.getInt("numKeyPoint"));
					trlevel_01_body.setVer(rs.getInt("ver"));
					trlevel_01_body.setTcuid(rs.getInt("tcuid"));
					trlevel_01_body.setTcName(rs.getString("tcName"));
					trlevel_01_body.setTcKeyPoint(rs.getString("tcKeyPoint"));
					trlevel_01_body.setTcResExp(rs.getString("tcResExp"));
					
					/** 取得測試人員送審案例時的註解 **/
					if (!"tester".equalsIgnoreCase(submittedBy)){
						strSQL = "SELECT comment FROM tms_mrcomment WHERE type=2 AND tpid = ? AND mrid = ? AND ver = ? AND creatorGroup = ? ORDER BY sn DESC";
						ps = conn.prepareStatement(strSQL);
						ps.setInt(1, tpid);
						ps.setString(2, mrid);
						ps.setInt(3, ver);
						ps.setString(4, "tester");
						rs = ps.executeQuery();
						
						if (rs.next()) {
							comment = rs.getString("comment");
							System.out.println(comment);
						}
					}
					trlevel_01_body.setComment(comment);
					trlevel_01_bodys.add(trlevel_01_body);
				}
			}
			returnResult = new Gson().toJson(trlevel_01_bodys);
			returnResult = returnResult.substring(1, returnResult.length());
			returnResult = "{\"sqlData\":["+returnResult+",\"strSQL\":\""+strSQL+"****"+tpid+"\",\"size\":\""+trlevel_01_bodys.size()+"\"}";
		} catch (SQLException e) {
			returnResult = "{\"errorMsg\":\""+wid.keepErrorMes(conn, ps, e,"", true)+"\"}";
		} finally {
			try {
				if (rs!= null) {rs.close();}
				if (rs2!= null) {rs2.close();}
				if (ps!= null) {ps.close();}
				if (conn!= null) {conn.close();}
			} catch (SQLException e) {
				returnResult = "{\"errorMsg\":\""+e.toString()+"\"}";
			}
		}
		
		response.setContentType("application/json");
		response.setCharacterEncoding("UTF-8");
		response.getWriter().write(returnResult);	
	}
}

class TRlevel_01_body {
	
	private String edition;
	private int trSN;
	private String trValue;
	private String trName;
	private int retenum;
	private int succnum;
	private int failnum;
	private int cantnum;
	private int waitnum;
	private int numKeyPoint;
	private int tpid;
	private int mrid;
	private int tcuid;
	private int ver;
	private String tcName;
	private String tcKeyPoint;
	private String tcResExp;
	private String comment;
	
	
	public String getComment() {
		return comment;
	}
	public void setComment(String comment) {
		this.comment = comment;
	}
	public String getEdition() {
		return edition;
	}
	public void setEdition(String edition) {
		this.edition = edition;
	}
	public int getRetenum() {
		return retenum;
	}
	public void setRetenum(int retenum) {
		this.retenum = retenum;
	}
	public int getSuccnum() {
		return succnum;
	}
	public void setSuccnum(int succnum) {
		this.succnum = succnum;
	}
	public int getFailnum() {
		return failnum;
	}
	public void setFailnum(int failnum) {
		this.failnum = failnum;
	}
	public int getCantnum() {
		return cantnum;
	}
	public void setCantnum(int cantnum) {
		this.cantnum = cantnum;
	}
	public int getWaitnum() {
		return waitnum;
	}
	public void setWaitnum(int waitnum) {
		this.waitnum = waitnum;
	}
	public int getNumKeyPoint() {
		return numKeyPoint;
	}
	public void setNumKeyPoint(int numKeyPoint) {
		this.numKeyPoint = numKeyPoint;
	}
	public int getTpid() {
		return tpid;
	}
	public void setTpid(int tpid) {
		this.tpid = tpid;
	}
	public int getMrid() {
		return mrid;
	}
	public void setMrid(int mrid) {
		this.mrid = mrid;
	}
	public int getTcuid() {
		return tcuid;
	}
	public void setTcuid(int tcuid) {
		this.tcuid = tcuid;
	}
	public int getVer() {
		return ver;
	}
	public void setVer(int ver) {
		this.ver = ver;
	}
	public String getTcName() {
		return tcName;
	}
	public void setTcName(String tcName) {
		this.tcName = tcName;
	}
	public String getTcKeyPoint() {
		return tcKeyPoint;
	}
	public void setTcKeyPoint(String tcKeyPoint) {
		this.tcKeyPoint = tcKeyPoint;
	}
	public String getTcResExp() {
		return tcResExp;
	}
	public void setTcResExp(String tcResExp) {
		this.tcResExp = tcResExp;
	}
	public int getTrSN() {
		return trSN;
	}
	public void setTrSN(int trSN) {
		this.trSN = trSN;
	}
	public String getTrValue() {
		return trValue;
	}
	public void setTrValue(String trValue) {
		this.trValue = trValue;
	}
	public String getTrName() {
		return trName;
	}
	public void setTrName(String trName) {
		this.trName = trName;
	}
	
}